/**
 * @author Ann
 * @version v1.0
 * @ClassName UpsertTaskWithLock
 * @Description
 * @date Created in 2024/7/31
 **/

import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

@Slf4j
public class UpsertTaskWithLock implements Runnable {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/test";
    private static final String USER = "root";
    private static final String PASS = "ancrane@2010";

    public void run() {
        int i = 0;
        while (true) {
            try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
                conn.setAutoCommit(false);

                // 获取锁
                String lockSql = "SELECT * FROM users WHERE id = ? FOR UPDATE";
                try (PreparedStatement lockPstmt = conn.prepareStatement(lockSql)) {
                    lockPstmt.setInt(1, 1);
                    lockPstmt.executeQuery();
                }

                // 执行UPSERT操作
                String upsertSql = "INSERT INTO users (id, name, email) VALUES (?, ?, ?) " +
                        "ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email)";
                try (PreparedStatement upsertPstmt = conn.prepareStatement(upsertSql)) {
                    upsertPstmt.setInt(1, 1);
                    upsertPstmt.setString(2, "Ann" + i);
                    upsertPstmt.setString(3, "ann@example.com");
                    upsertPstmt.executeUpdate();
                    log.info("更新完毕 name {}", "Ann" + i);
                }

                conn.commit();
                Thread.sleep(500);  // 睡眠1秒
            } catch (SQLException | InterruptedException e) {
                e.printStackTrace();
            }
            i++;
        }
    }

    public static void main(String[] args) {
        new Thread(new UpsertTaskWithLock()).start();
    }
}

